package Server;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
 * Database enumeration, store class name and URL of driver
 * @see #ODBC
 * @see #MySQL
 * @see #JavaDB
 * @see #Oracle
 */
public enum Database {

    ODBC("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:dbname"),
    MySQL("com.mysql.jdbc.Driver",
    "jdbc:mysql://mekong.rmit.edu.vn/s3312332", "s3312332", "qwerty1234"),
    JavaDB("org.apache.derby.jdbc.ClientDriver",
    "jdbc:derby://localhost:1527/MyIM", "user", "pass"),
    Oracle("sun.jdbc.driver.OracleDriver", "");
    private String className, url, username, password;
    private DatabaseMetaData metaData;
    private Statement stm = null;
    private PreparedStatement preStm = null;
    private ResultSet result = null;

    /**
     * Construct new Database
     * @param className class name to load driver
     * @param url       URL to connect to database
     * @see #ODBC
     * @see #MySQL
     * @see #JavaDB
     * @see #Oracle
     */
    Database(String className, String url) {
        this.className = className;
        this.url = url;
    }

    /**
     *
     * @param className class name to load driver
     * @param url       URL to connect to database
     * @param user      username to access database
     * @param pass      password to access database
     * @see #ODBC
     * @see #MySQL
     * @see #JavaDB
     * @see #Oracle
     */
    private Database(String className, String url, String user, String pass) {
        this.className = className;
        this.url = url;
        this.username = user;
        this.password = pass;
    }

    /**
     * Load driver for database
     */
    public void load() {
        try {
            Class.forName(className);
        } catch (ClassNotFoundException ex) {
            System.err.println("Driver not found...");
            System.exit(0);
        }
    }

    //<editor-fold defaultstate="collapsed" desc="Connection">
    /**
     * Connect to database at specific URL with username and password
     * @return the Connection object
     */
    public Connection connect() {
        Connection conn = null;
        try {
            if (username == null || username.isEmpty())
                conn = DriverManager.getConnection(url);
            else
                conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            System.err.println(
                    "Can not connect to database...");
            System.exit(0);
        }
        return conn;
    }

    /**
     * Rollback all changes made in the current transaction
     * @param conn Connection to rollback
     */
    private void rollback(Connection conn) {
        try {
            if (conn != null)
                conn.rollback();
        } catch (SQLException ex) {
            System.err.println("Database rollback... " + ex);
        }
    }

    /**
     * Close the connection if it is not null
     * @param conn the connection to close
     */
    public void close(Connection conn) {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException ex) {
            System.err.println("Close Connection... " + ex);
            System.exit(0);
        }
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="SQL">
    public boolean init(Connection conn, String tableName, String sql) {
        try {
            metaData = conn.getMetaData();
            result = metaData.getTables(null, null, tableName, null);
            if (!result.next()) {
                stm = conn.createStatement();
                stm.executeUpdate(sql);
                return true;
            }
        } catch (SQLException ex) {
            System.err.println("Initializing database..." + ex);
        } finally {
            reset();
        }
        return false;
    }

    /**
     * Create the prepared statement with parameters initialized
     * @param conn the Connection
     * @param sql  the SQL command
     * @param keys the SQL parameters
     * @return the PreparedStatement, null if SQLException occurs
     */
    private PreparedStatement prepare(
            Connection conn, String sql, Object[] keys) {
        try {
            preStm = conn.prepareStatement(sql);
            for (int i = 0; i < keys.length; i++) {
                Object obj = keys[i];
                if (obj instanceof String)
                    preStm.setString(i + 1, (String) obj);
                else if (obj instanceof Blob)
                    preStm.setBlob(i + 1, (Blob) obj);
                else if (obj == null)
                    preStm.setNull(i + 1, Types.BLOB);
            }
            return preStm;
        } catch (SQLException ex) {
            System.err.println("Creating statement... " + ex);
        }
        return null;
    }

    /**
     * Execute query to count data
     * @param conn the Connection
     * @param sql  the SQL command
     * @param keys the SQL parameters
     * @return number of data counted
     * @see #prepare
     */
    public int count(Connection conn, String sql, Object[] keys) {
        try {
            preStm = prepare(conn, sql, keys);
            result = preStm.executeQuery();
            result.next();
            return result.getInt(1);
        } catch (SQLException ex) {
            System.err.println("Select... " + ex);
        }
        return -1;
    }

    /**
     * Execute query to retrieve information from database
     * @param conn the Connection
     * @param sql  the SQL command
     * @param keys the SQL parameters
     * @return the ResultSet object, null if SQLException occurs
     * @see #prepare
     */
    public ResultSet query(Connection conn, String sql, Object[] keys) {
        try {
            preStm = prepare(conn, sql, keys);
            return result = preStm.executeQuery();
        } catch (SQLException ex) {
            System.err.println("Query... " + ex);
        }
        return null;
    }

    /**
     * Execute update information to database
     * @param conn the Connection
     * @param sql  the SQL command
     * @param keys the SQL parameters
     * @return boolean false if SQLException occurs
     * @see #prepare
     * @see #rollback
     */
    public boolean update(Connection conn, String sql, Object[] keys) {
        try {
            preStm = prepare(conn, sql, keys);
            preStm.executeUpdate();
            conn.commit();
            return true;
        } catch (SQLException ex) {
            System.err.println("Update... " + ex);
            rollback(conn);
        }
        return false;
    }

    /**
     * Release objects after used
     */
    public void reset() {
        try {
            if (stm != null)
                stm.close();
            else if (preStm != null)
                preStm.close();
            if (result != null)
                result.close();
        } catch (SQLException ex) {
            System.err.println("Closing statement... " + ex);
        }
    }
    //</editor-fold>
}
